package bupt.party.online.dao.impl;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Set;

import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.springframework.orm.hibernate3.HibernateTemplate;

import bupt.party.online.dao.IStudentDao;
import bupt.party.online.model.Branch;
import bupt.party.online.model.Student;

public class StudentDaoImpl implements IStudentDao {
	private HibernateTemplate hibernateTemplate;

	public HibernateTemplate getHibernateTemplate() {
		return hibernateTemplate;
	}

	public void setHibernateTemplate(HibernateTemplate hibernateTemplate) {
		this.hibernateTemplate = hibernateTemplate;
	}

	public List<Student> select(Student beanObject) {
		// TODO Auto-generated method stub
		return null;
	}

	public boolean delete(Student beanObject) {
		// TODO Auto-generated method stub
		return false;
	}

	public boolean insert(Student beanObject) {
		// TODO Auto-generated method stub
		return false;
	}

	public boolean saveOrUpdate(Student beanObject) {
		// TODO Auto-generated method stub
		return false;
	}

	public List<Student> queryByCriteria(DetachedCriteria criteria) {
		// TODO Auto-generated method stub
		return null;
	}

	public void delete(Long id) {
		// TODO Auto-generated method stub
		
	}

	public void save(Student user) {
		// TODO Auto-generated method stub
		
	}

	public void update(Student user) {
		// TODO Auto-generated method stub
		
	}

	public List<Student> findAll() {
		// TODO Auto-generated method stub
		return null;
	}

	public List<Student> findBySqlString(String sql) {
		// TODO Auto-generated method stub
		return null;
	}

	public List<Student> find(String queryString, Object... values) {
		// TODO Auto-generated method stub
		return null;
	}

	public Integer getRecordAcount(DetachedCriteria criteria) {
		// TODO Auto-generated method stub
		return null;
	}

	public List<Student> findPageResult(DetachedCriteria criteria,
			int firstResult, int maxResult) {
		// TODO Auto-generated method stub
		return null;
	}

	public Student findStudentByUserIdAndPassword(String userId, String password) {
		String sql = "from Student where  isDelete=0 and  studentNo='"+userId+"' and studentPassword='"+password+"'";
		List<Student> studentList = (List<Student>) this.hibernateTemplate.find(sql);
		if(studentList.size()>0){
			return studentList.get(0);
		}else{
			return null;
		}
		
	}

	@Override
	public void saveOrUpdateStudent(Student student) {
		this.hibernateTemplate.saveOrUpdate(student);
		
	}

	@Override
	public Student findStudentByStudentNO(String studentNO) {
		String sql = "from Student where studentNo = '"+studentNO+"'";
		List<Student> studentList = (List<Student>) this.hibernateTemplate.find(sql);
		if(studentList.size()>0){
			return studentList.get(0);
		}else{
			return null;
		}
		
	}

	@Override
	public List<Student> findAllStudent() {
		String sql = "from Student where isDelete=0";
		List<Student> list = this.hibernateTemplate.find(sql);
		
		return list;
	}

	

	@Override
	public int getStudentCountByBranchId(int branchId) {
		int count = 0;
		
		String sql = "select count(*) from student where is_delete=0";
		
		String sql2 = "select count(*) from Student where is_delete=0 and student_branchId="+branchId+" " +
				"union select  count(*) from student where is_delete=0  and  student_branchId in" +
				"(select id from branch where is_delete=0 and parent_id="+branchId+")";
		
		
		Session session = this.hibernateTemplate.getSessionFactory().getCurrentSession();
		try{
			Query q;
			if(branchId==1){
				q = session.createSQLQuery(sql);
			}else{
				q = session.createSQLQuery(sql2);
			}
			
			List list = q.list();
			if(list!=null &&list.size()>0){
				for(int i=0;i<list.size();i++){
					count+= ((BigInteger)list.get(i)).intValue();
				}
			
				return count;
			}else{
				return 0;
			}
			
			
		}catch(Exception e){
			e.printStackTrace();
			return 0;
		}
		
	}

	@Override
	public List<Student> findStudentByBranchId(Integer branchId) {
		
		List<Student>  student_list = new ArrayList<Student>();
		
		String sql = "select student.*,branch_name from student, branch where student.is_delete=0 and student_branchId=branch.id and branch.is_delete=0   order by student.student_no";

		
		String sql2 = "select * from (select student.*,branch_name from student, branch where student.is_delete=0 and student_branchId=branch.id and student_branchId="+branchId+" "  +
				" union select  student.*,branch_name  from student, branch where student. is_delete=0  and student_branchId=branch.id and student_branchId in" +
				"(select id from branch where branch.is_delete=0 and parent_id="+branchId+") )as  a order by student_no asc";
		
		Session session = this.hibernateTemplate.getSessionFactory().getCurrentSession();
		
		try{
			Query q;
			if(branchId==1){
				q = session.createSQLQuery(sql);
			}else{
				q = session.createSQLQuery(sql2);
			}
			
			List list = q.list();
			
			if(list!=null &&list.size()>0){
				for(int i=0;i<list.size();i++){
					Object[] obj = (Object[])list.get(i);
					
					Student student =new Student();
					student.setId((Integer)obj[0]);//id
					student.setStudentNo((String)obj[1]);//学号
					student.setStudentName((String)obj[3]);//姓名
					student.setStudentGender((String)obj[4]);//性别
					student.setStudentIdentity((String)obj[5]);//身份证号
					student.setStudentNationality((String)obj[6]);//民族
					student.setStudentTelephone((String)obj[8]);//电话
					student.setStudentEmail((String)obj[9]);//email
					student.setStudentBranchId((Integer)obj[10]);//支部id
					student.setIsDelete((Integer)obj[11]);//删除标记
					student.setStudentLearnTime((Double)obj[14]);//学习时长
					student.setStudentBranchName((String)obj[16]);//支部名称
					
					student_list.add(student);
				}
				
				return student_list;
				
			}else{
				return null;
			}
			
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
		
	}

	@Override
	public List<Student> QueryByConditions(String queryString, Object[] values,
			int firstResult, int pageSize) {
		Session session = this.hibernateTemplate.getSessionFactory().getCurrentSession();
		try{
			Query q = session.createQuery(queryString);
			
			if(values != null){
				for(int i=0;i<values.length;i++){
					q.setParameter(i, values[i]);
				}
			}
			
			q.setFirstResult(firstResult);
			
			q.setMaxResults(pageSize);
			
			return q.list();
			
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
		
		
	}

	@Override
	public List<Student> getResultsByPage(int firstResult, int pageSize) {
		
		Session session = this.hibernateTemplate.getSessionFactory().getCurrentSession();
		try{
			
			String sql = "from Student   order by studentNo asc";
			Query query = session.createQuery(sql);
			query.setFirstResult(firstResult);
			query.setMaxResults(pageSize);
			
			return query.list();
			
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public List<Student> QueryStudentByBranchId(int branchId, int firstResult,
			int pageSize) {
		Session session = this.hibernateTemplate.getSessionFactory().getCurrentSession();
		try{
			
			String sql = "from Student where isDelete=0  and  studentBranchId= "+branchId+" order by studentNo asc";
			Query query = session.createQuery(sql);
			query.setFirstResult(firstResult);
			query.setMaxResults(pageSize);
			
			return query.list();
			
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public DetachedCriteria getDetachedCriteria(Integer highLevelQuery,
			String queryContent, Integer firstpa, Integer secondpa) {
		
		
		DetachedCriteria criteria = DetachedCriteria.forClass(Student.class);
		criteria.addOrder(Order.asc("studentNo"));  //按照工号升序排序

		// 当用户没有做任何选择进行查询时，应该返回所有的数据信息
		if (highLevelQuery == -1 && firstpa == -1
				&& secondpa == -1) {
			return criteria;

		} else{ // 用户输入了一些选择条件

			// 首先判断高级搜索是否选了姓名、学号或者身份证中的一个，就进行如下的设置
			if (highLevelQuery != -1 && queryContent != ""
					&& queryContent.trim().length() > 0) {
				if (highLevelQuery == 1) { // 按照姓名
					criteria.add(Restrictions.like("studentName", "%" + queryContent.trim() + "%"));

				} else if (highLevelQuery == 2) { // 按照学号
					criteria.add(Restrictions.like("studentNo", "%"
							+ queryContent.trim() + "%"));

				} else if (highLevelQuery == 3) { // 按照身份证
					criteria.add(Restrictions.like("studentIdentity", "%"
							+ queryContent.trim() + "%"));
				}
			}

			

			if (secondpa != -1) { // 用户选到了支部
				criteria.add(Restrictions.eq("studentBranchId", secondpa));

			}else {
				if (firstpa != -1){
					String sql = "from Branch where isDelete=0 and parentId="+firstpa;
					List<Branch> list = this.hibernateTemplate.find(sql);
					
					
					
					
					if(list!=null && list.size()>0){//分党委下面有党支部
						List<Integer> idList = new ArrayList<Integer>();
						
						for(int j=0;j<list.size();j++){
							idList.add(list.get(j).getId());
						}
						
						int i = 0;
						Integer[] idArray = new Integer[list.size()];
						for (Integer integer : idList) {
							idArray[i++] = integer;
						}
						
						criteria.add(Restrictions.in("studentBranchId", idArray));
						
					}else{//当分党委下面没有支部的时候，特殊情况考虑，避免异常的发生。
						
						Integer[] idArray = new Integer[1];
						idArray[0]=-1;//添加一个-1的id号
						criteria.add(Restrictions.in("studentBranchId", idArray));
						
					}
					
					
				}
			
			}
		}
		
		return criteria;
	}

	@Override
	public int queryStudentCountWithCriteria(DetachedCriteria detachedCriteria) {
		
		Session session = this.hibernateTemplate.getSessionFactory().getCurrentSession();
		try{
			Criteria criteria = detachedCriteria
					.getExecutableCriteria(session);
			return Integer.parseInt(criteria.setProjection(Projections.rowCount())
					.uniqueResult().toString());
		}catch(Exception e) {
			e.printStackTrace();
			return 0;
		} 
	}

	@Override
	public List<Student> queryStudentWithCriteria(DetachedCriteria detachedCriteria,
			int firstResult, int maxResults) {
		
		List<Student> list;
		
		if(maxResults == 0){//这个时候查询所有的记录
			list = this.hibernateTemplate.findByCriteria(detachedCriteria);
		}else{//查询一定数量的分页记录
			list = this.hibernateTemplate.findByCriteria(detachedCriteria, firstResult, maxResults);
		}
		return list;
	}

	@Override
	public Student findStudentByStudentNOAndBranchId(String studentNO,
			int branchId) {
		
		
		Session session = this.hibernateTemplate.getSessionFactory().openSession();
		try{
			
			String sql = "from Student where is_delete=0  and  student_no='"+studentNO+"'  " +
					"and  student_branchId  in ("+branchId+")";
			
			Query query = session.createQuery(sql);
			
			return query.list().size()==0?null:(Student) query.list().get(0);
			
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
		
	}

	@Override
	public Student findStudentByStudentNOAndBranchIds(String studentNO,
			Set<Integer> branch_ids) {
		
		String branch_ids_toString = "";//先拼一下成为字符串
		String sub_branch_ids = "";
		
		if(branch_ids.size()>0){
			
			for(Iterator<Integer> itor= branch_ids.iterator(); itor.hasNext();) {
				   Integer i = (Integer)itor.next();
				   branch_ids_toString+= i +",";
				}
			//去掉最后的“，”
			sub_branch_ids = branch_ids_toString.substring(0,branch_ids_toString.length()-1);
		}
		
		if("".equals(sub_branch_ids))  sub_branch_ids = "-1";
		
		
		Session session = this.hibernateTemplate.getSessionFactory().openSession();
		try{
			
			String sql = "from Student where is_delete=0  and  student_no='"+studentNO+"'  " +
					"and  student_branchId  in ("+sub_branch_ids+")";
			
			Query query = session.createQuery(sql);
			
			return query.list().size()==0?null:(Student) query.list().get(0);
			
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
	}

	
	
	

	
}
